package com.zatech.cgnci.project.base.utils;

import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.zatech.cgnci.project.base.enums.ResponseCodeEnum;
import com.zatech.cgnci.project.base.enums.ValueDataTypeEnum;
import com.zatech.cgnci.project.base.exception.GlobalException;
import com.zatech.cgnci.project.entity.ExcelHeader;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;

/**
 * <p>
 * 类 DownloadTemplateHelper 的实现描述：excel模板工具类
 * </p>
 *
 * @author za-xiaojingjie
 * @version 2024/12/30 11:22
 * @since 2024/12/30 11:22
 */
@Component
public class DownloadTemplateHelper {
    /**
     * 创建excel模板
     * @param response http响应对象
     * @param templateName 生成模板名字
     * @param excelHeaders 表头值
     */
    public void createExcelTemplate(HttpServletResponse response, String templateName, List<ExcelHeader> excelHeaders) {
        //创建工作薄
        XSSFWorkbook wb = new XSSFWorkbook();

        // 单元格样式
        AtomicReference<XSSFCellStyle> titleStyle = new AtomicReference<>(wb.createCellStyle());
        titleStyle.get().setWrapText(true);

        // 红色字体样式
        XSSFFont redFont = wb.createFont();
        //设置字体
        redFont.setFontName("宋体");
        //设置字体大小
        redFont.setFontHeightInPoints((short) 12);
        redFont.setColor(new XSSFColor(IndexedColors.RED, null));

        // 字体样式
        XSSFFont font = wb.createFont();
        //设置字体
        font.setFontName("宋体");
        //设置字体大小
        font.setFontHeightInPoints((short) 12);

        // 设置为黄色背景
        XSSFCellStyle yellowStyle = wb.createCellStyle();
        yellowStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        yellowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 水平居中
        yellowStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        yellowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 水平居中
        yellowStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        yellowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        yellowStyle.setAlignment(HorizontalAlignment.CENTER);

        titleStyle.get().setFont(font);
        // 水平居中
        titleStyle.get().setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        titleStyle.get().setVerticalAlignment(VerticalAlignment.CENTER);

        //文本格式
        XSSFCellStyle textStyle = wb.createCellStyle();
        DataFormat format = wb.createDataFormat();
        textStyle.setDataFormat(format.getFormat("@"));

        //如果有多个sheet页，根据sheetNo分组
        Map<Integer, List<ExcelHeader>> groupBy = excelHeaders.stream()
                .collect(Collectors.groupingBy(ExcelHeader::getSheetNo));
        
        groupBy.values().forEach(item -> {
            //新建sheet
            XSSFSheet sheet = wb.createSheet(item.get(0).getSheetName());
            sheet.createFreezePane(0, 1, 0, 1);
            //生成sheet1内容
            //第一个sheet的第一行为标题
            XSSFRow rowFirst = sheet.createRow(0);
            //写标题
            List<List<String>> data = new ArrayList<>();
            List<String> examples = new ArrayList<>();
            List<String> defaults = new ArrayList<>();
            List<Integer> downColumns = new ArrayList<>();
            List<String[]> downDatas = new ArrayList<>();
            List<Integer> addrassDownColumns = new ArrayList<>();

            List<Integer> relationDownColumns = new ArrayList<>();
            


            List<String> relationToMains = new ArrayList<>();
            for (int i = 0; i < item.size(); i++) {
                ExcelHeader uploadExcelHeaderDTO = item.get(i);
                
                //处理name，若require=true，字段加红*
                uploadExcelHeaderDTO
                        .setName(uploadExcelHeaderDTO.getName() + (uploadExcelHeaderDTO.isRequired() ? "*" : ""));
                //获取第一行的每个单元格
                XSSFCell cell = rowFirst.createCell(i);
                //设置每列的列宽
                sheet.setColumnWidth(i, 4000);

                // 加样式
                cell.setCellStyle(titleStyle.get());
                // 表头必填字段标识符：*，加红
                if (uploadExcelHeaderDTO.getName().lastIndexOf('*') > 0) {
                    String text = uploadExcelHeaderDTO.getName();
                    XSSFRichTextString xssfRichTextString = new XSSFRichTextString(text);
                    xssfRichTextString.applyFont(text.length() - 1, text.length(), redFont);
                    //往单元格里写数据
                    cell.setCellValue(xssfRichTextString);
                } else {
                    //往单元格里写数据
                    cell.setCellValue(uploadExcelHeaderDTO.getName());
                }
                //处理下拉行
                if(!VerifyUtils.isEmpty(uploadExcelHeaderDTO.getOptionList())){
                    downColumns.add(i);
                    List<String> optionList = uploadExcelHeaderDTO.getOptionList();
                    downDatas.add(optionList.toArray(new String[0]));
                }
                sheet.setDefaultColumnStyle(i, textStyle);
                examples.add(Optional.ofNullable(uploadExcelHeaderDTO.getExampleValue()).orElse(""));
                defaults.add(Optional.ofNullable(uploadExcelHeaderDTO.getDefaultValue()).orElse(""));
                
            }

            //写示例行、默认值行
            boolean isExamples = CollectionUtils.isNotEmpty(examples) && CollectionUtils.isNotEmpty(
                    examples.stream().filter(dataStr -> StringUtils.isNotBlank(dataStr)).collect(Collectors.toList()));
            if (isExamples) {
                data.add(examples);
            }
            if (CollectionUtils.isNotEmpty(defaults)) {
                for (int i = 0; i < 100; i++) {
                    data.add(defaults);
                }
            }
            XSSFCellStyle dateStyle = wb.createCellStyle();
            dateStyle.setDataFormat(wb.createDataFormat().getFormat("yyyy/m/d"));
            dateStyle.setAlignment(HorizontalAlignment.CENTER);
            dateStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            XSSFCellStyle otherStype = wb.createCellStyle();
            otherStype.setDataFormat(wb.createDataFormat().getFormat("@"));
            otherStype.setAlignment(HorizontalAlignment.CENTER);
            otherStype.setVerticalAlignment(VerticalAlignment.CENTER);

            for (int j = 0; j < data.size(); j++) {
                XSSFRow rowData = sheet.createRow(j + 1);
                for (int i = 0; i < data.get(j).size(); i++) {
                    //获取第一行的每个单元格
                    XSSFCell cell = rowData.createCell(i);
                    //设置每列的列宽
                    sheet.setColumnWidth(i, 4000);
                    if (isExamples && j == 0) {
                        // 加样式
                        if (ValueDataTypeEnum.INSURED_FACTOR_TIME.getCode().equals(item.get(i).getValueDataType())) {
                            //日期数据类型是日期样式
                            yellowStyle.setDataFormat(wb.createDataFormat().getFormat("yyyy/m/d"));
                        } else {
                            yellowStyle.setDataFormat(wb.createDataFormat().getFormat("@"));
                        }
                        cell.setCellStyle(yellowStyle);
                    } else {
                        //加样式
                        if (ValueDataTypeEnum.INSURED_FACTOR_TIME.getCode().equals(item.get(i).getValueDataType())) {
                            //日期数据类型是日期样式
                            cell.setCellStyle(dateStyle);
                        } else {
                            cell.setCellStyle(otherStype);
                        }
                    }
                    //为单元格添加公式
                    if (StringUtils.isNotEmpty(item.get(i).getFormula()) && (!isExamples || j != 0)) {
                        String formula = item.get(i).getFormula();
                        //customertype特殊逻辑
                        //其他替换行号
                        if (item.get(i).getFormula().contains("{}")) {
                            formula = formula.replaceAll("\\{}", String.valueOf(rowData.getRowNum() + 1));
                        }

                        if (formula.startsWith("=")) {
                            cell.setCellFormula(formula.replaceFirst("=", ""));
                        } else {
                            cell.setCellFormula(formula);
                        }

                    } else {
                        //往单元格里写数据
                        cell.setCellValue(data.get(j).get(i));
                    }
                }
            }
            //下拉处理
            this.handleDown(downColumns, downDatas, sheet, sheet.getSheetName(), wb);
        });
        try  {
            //文件名
            String fileName = templateName;
            //确保中文文件名可以正常展示
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/octet-stream;charset=UTF-8");
            fileName= URLEncoder.encode(fileName, "UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
            OutputStream out = new BufferedOutputStream(response.getOutputStream());
            out.flush();
            wb.write(out);
            out.close();
        } catch (Exception e) {
            throw new GlobalException(ResponseCodeEnum.HTTP_REQUEST_SERVICE_ERROR,"生成excel失败");
        }

    }

    private void handleDown(List<Integer> downColumns, List<String[]> downDatas, Sheet sheet, String sheetName,
                            Workbook wb) {
        for (int r = 0; r < downColumns.size(); r++) {
            //获取下拉对象
            String[] dlData = downDatas.get(r);
            int rownum = downColumns.get(r);
            //255以内的下拉
            if (dlData != null && dlData.length > 0) {
                    createDropDownList(sheet, dlData, 1, 50000, rownum, rownum);
            }
        }
    }

    /**
     * 列表下拉处理（）
     */
    public static void createDropDownList(Sheet sheet, String[] values, int firstRow, int lastRow, int firstCol,
                                          int lastCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DataValidationConstraint constraint = helper.createExplicitListConstraint(values);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        if (dataValidation instanceof HSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(false);
        } else {
            dataValidation.setSuppressDropDownArrow(true);
        }
        sheet.addValidationData(dataValidation);
    }
}
